using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TrainTicket.VO;

namespace TrainTicket.DAO
{
    public class RouterDAO
    {
        private DbConnection _conn;

        public RouterDAO()
        {
            _conn = new DbConnection();
        }

        public DataTable GetAllStartPlace()
        {
            return _conn.ExecuteDataTable("sp_GetAllStartPlace", null);
        }

        public DataTable GetAllDestinationPlace()
        {
            return _conn.ExecuteDataTable("sp_GetAllDestinationPlace", null);
        }

        public DataTable GetAllDestinationPlaceByStartPlace(string startPlace)
        {
            SqlParameter param = new SqlParameter("@StartPlace", SqlDbType.NVarChar, 50);
            param.Value = startPlace;
            return _conn.ExecuteDataTable("sp_GetAllDestinationPlaceByStartPlace", param);
        }

        public DataTable GetAllRouter()
        {
            return _conn.ExecuteDataTable("sp_GetAllRouter", null);
        }

        public int InsertRouter(Router router)
        {
            SqlParameter[] param = new SqlParameter[7];

            param[0] = new SqlParameter("@RouterName", SqlDbType.NVarChar, 50);
            param[0].Value = router.RouterName;

            param[1] = new SqlParameter("@StartPlace", SqlDbType.NVarChar, 50);
            param[1].Value = router.StartPlace;

            param[2] = new SqlParameter("@DestinationPlace", SqlDbType.NVarChar, 50);
            param[2].Value = router.DestinationPlace;

            param[3] = new SqlParameter("@Long", SqlDbType.Int);
            param[3].Value = router.Long;

            param[4] = new SqlParameter("@Description", SqlDbType.NText);
            param[4].Value = router.Description;

            param[5] = new SqlParameter("@CreateDate", SqlDbType.DateTime);
            param[5].Value = router.CreateDate;

            param[6] = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            param[6].Value = router.Status;

            return _conn.ExecuteNonQuery("sp_InsertRouter", param);
        }

        public int DeleteRouter(Router router)
        {
            SqlParameter param = new SqlParameter();
            param = new SqlParameter("@RouterID", SqlDbType.Int);
            param.Value = router.RouterID;
            return _conn.ExecuteNonQuery("sp_DeleteRouter", param);
        }

        public DataTable GetRouterById(int routerID)
        {
            SqlParameter param = new SqlParameter("@RouterID", SqlDbType.Int);
            param.Value = routerID;
            return _conn.ExecuteDataTable("sp_GetRouterById", param);
        }

        public int CheckRouterExitsInsert(string routerName)
        {
            SqlParameter param = new SqlParameter("@RouterName", SqlDbType.NVarChar, 50);
            param.Value = routerName;
            return _conn.ExecuteScalar("sp_CheckRouterExitsInsert", param);
        }

        public int CheckRouterExitsUpdate(int routerID, string routerName)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@RouterID", SqlDbType.Int);
            param[0].Value = routerID;

            param[1] = new SqlParameter("@RouterName", SqlDbType.NVarChar, 50);
            param[1].Value = routerName;

            return _conn.ExecuteScalar("sp_CheckRouterExitsUpdate", param);
        }

        public int UpdateRouter(Router router)
        {
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@RouterID", SqlDbType.Int);
            param[0].Value = router.RouterID;

            param[1] = new SqlParameter("@RouterName", SqlDbType.NVarChar, 50);
            param[1].Value = router.RouterName;

            param[2] = new SqlParameter("@StartPlace", SqlDbType.NVarChar, 50);
            param[2].Value = router.StartPlace;

            param[3] = new SqlParameter("@DestinationPlace", SqlDbType.NVarChar, 50);
            param[3].Value = router.DestinationPlace;

            param[4] = new SqlParameter("@Long", SqlDbType.Int);
            param[4].Value = router.Long;

            param[5] = new SqlParameter("@Description", SqlDbType.NText);
            param[5].Value = router.Description;

            param[6] = new SqlParameter("@CreateDate", SqlDbType.DateTime);
            param[6].Value = router.CreateDate;

            param[7] = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            param[7].Value = router.Status;

            return _conn.ExecuteNonQuery("sp_UpdateRouter", param);
        }
    }
}
